set doc off
set verify off
--
--    Program: CAPGLEXT_CALENDAR.sql
--    Description:
--    Extract calendar information
--
--    Revision:
--    1st
--    Parameter: 1. set of books short name e.g. SOB_C999


--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';

declare
  l_count			number;
  l_processed_count	number;
  l_rejected_count	number;
  l_err_msg       	varchar2(2000);

  l_utl_file  		utl_file.file_type;
  l_filename  		varchar2(255);
  l_pathname  		varchar2(100);

  l_ngo_sob			varchar2(20);

  cursor cur_cal (p_sob varchar2) is
    select *
    from   gl_periods
    where  period_set_name = (select period_set_name
                              from   gl_sets_of_books
	                      	  where  upper(short_name) = upper(p_sob));

begin
  l_pathname := '/tmp';
  l_filename := 'CAPGL_CALENDAR.dat';
  l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w', 32767);

  l_count := 0;
  l_processed_count := 0;
  l_rejected_count  := 0;

  l_ngo_sob := '&1';

  select count(1)
  into   l_count
  from   gl_sets_of_books
  where  upper(short_name) = upper(l_ngo_sob);

  if l_count = 0 then
    dbms_output.put_line(l_ngo_sob||': set of books not found');
  end if;

  for cur in cur_cal(l_ngo_sob) loop

    -- output data
    utl_file.put_line( l_utl_file,
      cur.period_set_name|| chr(9) ||
      cur.period_name|| chr(9) ||
        cur.last_update_date|| chr(9) ||
      	cur.last_updated_by|| chr(9) ||
      cur.start_date|| chr(9) ||
      cur.end_date|| chr(9) ||
      cur.year_start_date|| chr(9) ||
      cur.quarter_start_date|| chr(9) ||
      cur.period_type|| chr(9) ||
      cur.period_year|| chr(9) ||
      cur.period_num|| chr(9) ||
      cur.quarter_num|| chr(9) ||
      cur.entered_period_name|| chr(9) ||
      cur.adjustment_period_flag|| chr(9) ||
      cur.creation_date|| chr(9) ||
      	cur.created_by|| chr(9) ||
      cur.description|| chr(9) ||
      cur.attribute1|| chr(9) ||
      cur.attribute2|| chr(9) ||
      cur.attribute3|| chr(9) ||
      cur.attribute4|| chr(9) ||
      cur.attribute5|| chr(9) ||
      cur.attribute6|| chr(9) ||
      cur.attribute7|| chr(9) ||
      cur.attribute8|| chr(9) ||
      cur.context|| chr(9) ||
      CAPBREXT_COMMON.GetUser(cur.last_updated_by)|| chr(9) ||
      CAPBREXT_COMMON.GetUser(cur.created_by)
    );

    l_processed_count := l_processed_count + 1;
  end loop;

  if l_processed_count = 0 then
    l_rejected_count := l_rejected_count + 1;
    l_err_msg := l_ngo_sob || ': no calendar defined for the selected set of books or no such set of books';
  end if;

  -- insert into log table
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg)
  values ( 'CAPGLEXT_CALENDAR', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );

  commit;

  utl_file.fclose(l_utl_file);
exception
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
